<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Populate DataTable - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span></li>
<li class="tocentry"><a href="Joins.htm">Projecting with a Join</a>
</li>
<li class="tocentry"><a href="Views.htm">Reading from Views</a>
</li>
<li class="tocentry"><a href="RowCount.htm">Row Counts</a>
</li>
<li class="tocentry"><a href="Pagination.htm">Paging Results</a>
</li>
<li class="tocentry current"><a class="current" href="PopulateDataTable.htm">Populate DataTable</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>
<li class="tocentry"><a href="ScalarValue.htm">Reading a Salar Value from a Row</a>
</li>
<li class="tocentry"><a href="SingleColumn.htm">Reading a Single Column from a Table</a>
</li>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></li> / <li><a href="PopulateDataTable.htm">Populate DataTable</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="populate-a-datatable">Populate a DataTable<a class="headerlink" href="#populate-a-datatable" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>A <code>DataTable</code> is often used for holding the results of a report that are then displayed in a data grid of some sort. It is primarily used when the columns returned are dynamically chosen or when creating custom classes for each report is consider to be onerous.</p>
<p>A <code>DataTable</code> may also be used as a staging area before performing a bulk insert operation. </p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IPopulateDataTableScenario

{
    /// &lt;summary&gt;
    /// Gets a filtered list of EmployeeClassification rows.
    /// &lt;/summary&gt;
    DataTable FindByFlags(bool isExempt, bool isEmployee);

    /// &lt;summary&gt;
    /// Gets all EmployeeClassification rows.
    /// &lt;/summary&gt;
    DataTable GetAll();
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p><code>DataTable.Load</code> can be provided with an <code>IDataReader</code>.</p>
<pre><code class="cs">public class PopulateDataTableScenario : SqlServerScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    { }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;&quot;;

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue(&quot;@IsExempt&quot;, isExempt);
            cmd.Parameters.AddWithValue(&quot;@IsEmployee&quot;, isEmployee);
            using (var reader = cmd.ExecuteReader())
                result.Load(reader);
        }
        return result;
    }

    public DataTable GetAll()
    {
        const string sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;&quot;;

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        using (var reader = cmd.ExecuteReader())
            result.Load(reader);

        return result;
    }
}
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>Chain natively supports <code>DataTable</code>.</p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    const string TableName = &quot;HR.EmployeeClassification&quot;;
    readonly SqlServerDataSource m_DataSource;

    public PopulateDataTableScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        return m_DataSource.From(TableName, new { isExempt, isEmployee }).ToDataTable().Execute();
    }

    public DataTable GetAll()
    {
        return m_DataSource.From(TableName).ToDataTable().Execute();
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p><code>DataTable.Load</code> can be provided with an <code>IDataReader</code>.</p>
<pre><code class="cs">public class PopulateDataTableScenario : ScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    {
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;&quot;;

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var reader = con.ExecuteReader(sql, new { isExempt, isEmployee }))
            result.Load(reader);

        return result;
    }

    public DataTable GetAll()
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;&quot;;

        var result = new DataTable();

        using (var con = OpenConnection())
        using (var reader = con.ExecuteReader(sql))
            result.Load(reader);

        return result;
    }
}
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class PopulateDataTableScenario : ScenarioBase, IPopulateDataTableScenario
{
    public PopulateDataTableScenario(string connectionString) : base(connectionString)
    {
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @isExempt AND ec.IsEmployee = @isEmployee;&quot;;

        return DbConnector.ReadToDataTable(sql, new { isExempt, isEmployee }).Execute();
    }

    public DataTable GetAll()
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;&quot;;

        return DbConnector.ReadToDataTable(sql).Execute();
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>EF Core does not support <code>DataTable</code>. </p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public PopulateDataTableScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.Add(&quot;EmployeeClassificationKey&quot;, typeof(int));
        dt.Columns.Add(&quot;EmployeeClassificationName&quot;, typeof(string));
        dt.Columns.Add(&quot;IsExempt&quot;, typeof(bool));
        dt.Columns.Add(&quot;IsEmployee&quot;, typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassification.Where(x =&gt; x.IsExempt == isExempt &amp;&amp; x.IsEmployee == isEmployee))
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.Add(&quot;EmployeeClassificationKey&quot;, typeof(int));
        dt.Columns.Add(&quot;EmployeeClassificationName&quot;, typeof(string));
        dt.Columns.Add(&quot;IsExempt&quot;, typeof(bool));
        dt.Columns.Add(&quot;IsEmployee&quot;, typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassification)
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }
}
</code></pre>

<p>You can generalize this using a reflection library.</p>
<pre><code class="cs">public class PopulateDataTableScenario2 : IPopulateDataTableScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public PopulateDataTableScenario2(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var propertyList = MetadataCache.GetMetadata&lt;EmployeeClassification&gt;().Properties.Where(p =&gt; p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassification.Where(x =&gt; x.IsExempt == isExempt &amp;&amp; x.IsEmployee == isEmployee))
            {
                for (var i = 0; i &lt; propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var propertyList = MetadataCache.GetMetadata&lt;EmployeeClassification&gt;().Properties.Where(p =&gt; p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassification)
            {
                for (var i = 0; i &lt; propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    static DataTable BuildGenericDataTable(List&lt;PropertyMetadata&gt; propertyList)
    {
        var dt = new DataTable();
        foreach (var property in propertyList)
        {
            var propertyType = MetadataCache.GetMetadata(property.PropertyType);
            if (propertyType.IsNullable &amp;&amp; propertyType.TypeInfo.IsValueType) //Special handling for Nullable&lt;T&gt;
            {
                var underlyingType = propertyType.TypeInfo.GenericTypeArguments[0];
                var col = dt.Columns.Add(property.Name, underlyingType);
                col.AllowDBNull = true;
            }
            else
            {
                var col = dt.Columns.Add(property.Name, property.PropertyType);
                col.AllowDBNull = property.IsReferenceNullable ?? propertyType.IsNullable;
            }
        }

        return dt;
    }
}
</code></pre>

<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>EF Core does not support <code>DataTable</code>. </p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public PopulateDataTableScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.Add(&quot;EmployeeClassificationKey&quot;, typeof(int));
        dt.Columns.Add(&quot;EmployeeClassificationName&quot;, typeof(string));
        dt.Columns.Add(&quot;IsExempt&quot;, typeof(bool));
        dt.Columns.Add(&quot;IsEmployee&quot;, typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassifications.Where(x =&gt; x.IsExempt == isExempt &amp;&amp; x.IsEmployee == isEmployee))
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.Add(&quot;EmployeeClassificationKey&quot;, typeof(int));
        dt.Columns.Add(&quot;EmployeeClassificationName&quot;, typeof(string));
        dt.Columns.Add(&quot;IsExempt&quot;, typeof(bool));
        dt.Columns.Add(&quot;IsEmployee&quot;, typeof(bool));

        using (var context = CreateDbContext())
        {
            foreach (var row in context.EmployeeClassifications)
                dt.Rows.Add(row.EmployeeClassificationKey, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }
        return dt;
    }
}
</code></pre>

<p>You can generalize this using a reflection library.</p>
<pre><code class="cs">public class PopulateDataTableScenario2 : IPopulateDataTableScenario
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public PopulateDataTableScenario2(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var propertyList = MetadataCache.GetMetadata&lt;EmployeeClassification&gt;().Properties.Where(p =&gt; p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassifications.Where(x =&gt; x.IsExempt == isExempt &amp;&amp; x.IsEmployee == isEmployee))
            {
                for (var i = 0; i &lt; propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    public DataTable GetAll()
    {
        var propertyList = MetadataCache.GetMetadata&lt;EmployeeClassification&gt;().Properties.Where(p =&gt; p.CanRead).ToList();

        var dt = BuildGenericDataTable(propertyList);

        using (var context = CreateDbContext())
        {
            var buffer = new object[propertyList.Count];
            foreach (var row in context.EmployeeClassifications)
            {
                for (var i = 0; i &lt; propertyList.Count; i++)
                    buffer[i] = propertyList[i].InvokeGet(row) ?? DBNull.Value;

                dt.Rows.Add(buffer);
            }
        }
        return dt;
    }

    static DataTable BuildGenericDataTable(List&lt;PropertyMetadata&gt; propertyList)
    {
        var dt = new DataTable();
        foreach (var property in propertyList)
        {
            var propertyType = MetadataCache.GetMetadata(property.PropertyType);
            if (propertyType.IsNullable &amp;&amp; propertyType.TypeInfo.IsValueType) //Special handling for Nullable&lt;T&gt;
            {
                var underlyingType = propertyType.TypeInfo.GenericTypeArguments[0];
                var col = dt.Columns.Add(property.Name, underlyingType);
                col.AllowDBNull = true;
            }
            else
            {
                var col = dt.Columns.Add(property.Name, property.PropertyType);
                col.AllowDBNull = property.IsReferenceNullable ?? propertyType.IsNullable;
            }
        }

        return dt;
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>LLBLGen Pro natively supports <code>DataTable</code>.</p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        using (var adapter = new DataAccessAdapter())
        {
            var q = new QueryFactory().EmployeeClassification
                          .Where(EmployeeClassificationFields.IsEmployee.Equal(isEmployee)
                                 .And(EmployeeClassificationFields.IsExempt.Equal(isExempt)))
                                      .Select(Projection.Full);
            return adapter.FetchAsDataTable(q);
        }
    }

    public DataTable GetAll()
    {
        using (var adapter = new DataAccessAdapter())
        {
            return adapter.FetchAsDataTable(new QueryFactory().EmployeeClassification.Select(Projection.Full));
        }
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>NHibernate does not support <code>DataTable</code>, but you can add it using an <code>IResultTransformer</code>. </p>
<pre><code class="cs">[SuppressMessage(&quot;Design&quot;, &quot;CA1001&quot;)]
public class DataTableResultTransformer : IResultTransformer
{
    readonly DataTable m_DataTable = new DataTable();
    readonly Type?[] m_DataTypeOverrides = Array.Empty&lt;Type?&gt;();

    /// &lt;summary&gt;
    /// Initializes a new instance of the &lt;see cref=&quot;DataTableResultTransformer&quot;/&gt; class.
    ///
    /// Only use this constructor if none of the columns are nullable.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Warning: If a field is NULL in the first row, that entire column will be cast as a String.&lt;/remarks&gt;
    public DataTableResultTransformer()
    {
    }

    /// &lt;summary&gt;
    /// Initializes a new instance of the &lt;see cref=&quot;DataTableResultTransformer&quot;/&gt; class.
    ///
    /// 1. If dataTypeOverrides for a given column is not null, it is used.
    /// 2. If the field is not null for the first row, then that field's data type if used.
    /// 3. If both the dataTypeOverride and the field in the first row are null, the column's data type is String.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;dataTypeOverrides&quot;&gt;The expected data types.&lt;/param&gt;
    public DataTableResultTransformer(params Type?[] dataTypeOverrides)
    {
        m_DataTypeOverrides = dataTypeOverrides;
    }

    public IList TransformList(IList collection)
    {
        return new List&lt;DataTable&gt; { m_DataTable };
    }

    public object TransformTuple(object[] tuple, string[] aliases)
    {
        if (tuple == null || tuple.Length == 0)
            throw new ArgumentException($&quot;{nameof(tuple)} is null or empty.&quot;, nameof(tuple));

        if (aliases == null || aliases.Length == 0)
            throw new ArgumentException($&quot;{nameof(aliases)} is null or empty.&quot;, nameof(aliases));

        if (m_DataTable.Columns.Count == 0)
        {
            //Create the DataTable if this is the first row
            for (var i = 0; i &lt; aliases.Length; i++)
            {
                var col = m_DataTable.Columns.Add(aliases[i]);
                if (i &lt; m_DataTypeOverrides.Length &amp;&amp; m_DataTypeOverrides[i] != null)
                    col.DataType = m_DataTypeOverrides[i]!;
                else if (tuple[i] != null &amp;&amp; tuple[i] != DBNull.Value)
                    col.DataType = tuple[i].GetType();
            }
        }

        return m_DataTable.Rows.Add(tuple);
    }
}
</code></pre>

<p>Note that inline SQL must be used inconjunction with the <code>IResultTransformer</code>.</p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    readonly ISessionFactory m_SessionFactory;

    public PopulateDataTableScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(EmployeeClassification classification)
    {
        if (classification == null)
            throw new ArgumentNullException(nameof(classification), $&quot;{nameof(classification)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(classification);
            session.Flush();
            return classification.EmployeeClassificationKey;
        }
    }

    public void DeleteByKey(int employeeClassificationKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(new EmployeeClassification() { EmployeeClassificationKey = employeeClassificationKey });
            session.Flush();
        }
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        //Note that this uses &quot;:ParameterName&quot; instead of SQL Server's normal &quot;@ParameterName&quot;.
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = :IsExempt AND ec.IsEmployee = :IsEmployee;&quot;;

        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            var sqlQuery = session.CreateSQLQuery(sql);
            sqlQuery.SetParameter(&quot;IsExempt&quot;, isExempt);
            sqlQuery.SetParameter(&quot;IsEmployee&quot;, isEmployee);
            var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
            return transformedQuery.List().OfType&lt;DataTable&gt;().Single();
        }
    }

    public DataTable GetAll()
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;&quot;;

        using (var session = m_SessionFactory.OpenStatelessSession())
        {
            var sqlQuery = session.CreateSQLQuery(sql);
            var transformedQuery = sqlQuery.SetResultTransformer(new DataTableResultTransformer());
            return transformedQuery.List().OfType&lt;DataTable&gt;().Single();
        }
    }
}
</code></pre>

<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>In RepoDb, the <code>DataTable.Load</code> can be provided with an <code>IDataReader</code> object from <code>ExecuteReader</code> method.</p>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    readonly string m_ConnectionString;

    public PopulateDataTableScenario(string connectionString)
    {
        m_ConnectionString = connectionString;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec WHERE ec.IsExempt = @IsExempt AND ec.IsEmployee = @IsEmployee;&quot;;
        var table = new DataTable();

        using (var connection = new SqlConnection(m_ConnectionString))
        {
            using (var reader = connection.ExecuteReader(sql, new { isExempt, isEmployee }))
            {
                table.Load(reader);
            }
        }

        return table;
    }

    public DataTable GetAll()
    {
        var sql = @&quot;SELECT ec.EmployeeClassificationKey, ec.EmployeeClassificationName, ec.IsExempt, ec.IsEmployee FROM HR.EmployeeClassification ec;&quot;;
        var table = new DataTable();

        using (var connection = new SqlConnection(m_ConnectionString))
        {
            using (var reader = connection.ExecuteReader(sql))
            {
                table.Load(reader);
            }
        }

        return table;
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class PopulateDataTableScenario : IPopulateDataTableScenario
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    
    public PopulateDataTableScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public DataTable FindByFlags(bool isExempt, bool isEmployee)
    {
        var dt = new DataTable();
        dt.Columns.AddRange( ModelDefinition&lt;EmployeeClassification&gt;.Definition.FieldDefinitions.Select(k =&gt;
            new DataColumn(k.FieldName, k.FieldType)).ToArray());
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var sql = db.From&lt;EmployeeClassification&gt;().Where(x =&gt; x.IsExempt == isExempt &amp;&amp; x.IsEmployee == isEmployee);
            foreach (var row in db.SelectLazy(sql))
                dt.Rows.Add(row.Id, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }

        return dt;
    }

    public DataTable GetAll()
    {
        var dt = new DataTable();
        dt.Columns.AddRange( ModelDefinition&lt;EmployeeClassification&gt;.Definition.FieldDefinitions.Select(k =&gt;
            new DataColumn(k.FieldName, k.FieldType)).ToArray());

        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            foreach (var row in db.SelectLazy(db.From&lt;EmployeeClassification&gt;()))
                dt.Rows.Add(row.Id, row.EmployeeClassificationName, row.IsExempt, row.IsEmployee);
        }

        return dt;
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
